import sys
import os
import random
from PySide6.QtWidgets import *
from PySide6.QtGui import *
from PySide6.QtCore import *
from PySide6.QtSql import *
class Demo(QWidget):
    def __init__(self, parent=None):
        super().__init__(parent)
        self.parent = parent
        self.dbFile=os.path.join(self.parent.getAppDir(),'test.db')
        layout = QVBoxLayout()

        toolBar=QToolBar()
        btn=QPushButton('OpenDb')
        btn.clicked.connect(self.Open)
        toolBar.addWidget(btn)

        btn=QPushButton('CreateTable')
        btn.clicked.connect(self.CreateTable)
        toolBar.addWidget(btn)

        btn=QPushButton('Add')
        btn.clicked.connect(self.Add)
        toolBar.addWidget(btn)        

        btn=QPushButton('Delete')
        btn.clicked.connect(self.Delete)
        toolBar.addWidget(btn)

        btn=QPushButton('Query')
        btn.clicked.connect(self.Query)
        toolBar.addWidget(btn)
        layout.addWidget(toolBar)

        self.text=QLineEdit()
        toolBar.addWidget(self.text)
        btn=QPushButton('Update')
        btn.clicked.connect(self.Update)
        toolBar.addWidget(btn)
        
        self.tableView=QTableView()
        self.tableView.setSelectionBehavior(QTableView.SelectionBehavior.SelectRows)#行选择        
        layout.addWidget(self.tableView)          

        self.setLayout(layout)
    def Open(self):
        self.db=QSqlDatabase().addDatabase("QSQLITE")
        self.db.setDatabaseName(self.dbFile)
        self.parent.OnShowMessage(f'{self.dbFile} open {self.db.open()}')
    def CreateTable(self):
        if self.db.isOpen() is True:
            query=QSqlQuery(self.db)
            query.exec('DROP TABLE IF EXISTS User')
            query.exec('CREATE TABLE User (Id integer NOT NULL,Name text)')
            self.parent.OnShowMessage('create table "User"')
        
    def Add(self):
        query=QSqlQuery(self.db)
        id=random.randint(1,100)
        #直接执行sql
        #query.exec(f'INSERT INTO User VALUES({id},"user{id}")')
        #参数化执行
        query.prepare('INSERT INTO User VALUES(:id,:name)')
        query.bindValue(':id',id)
        query.bindValue(':name',f'user{id}')        
        query.exec()
        self.parent.OnShowMessage(f'Add user{id}')
    def Update(self):
        index=self.tableView.currentIndex()
        if index is None and len(self.text.text())==0:return

        query=QSqlQuery(self.db)
        id = int(self.model.itemData(self.model.index(index.row(),0))[0])
        old=str(self.model.itemData(self.model.index(index.row(),1))[0])
        new=self.text.text()
        #直接执行sql
        #query.exec(f'UPDATE User SET Name="{new}" WHERE Id={id}')
        #参数化执行
        query.prepare('UPDATE User SET Name=? WHERE Id=?')
        query.addBindValue(new)
        query.addBindValue(id)
        query.exec()        
        self.parent.OnShowMessage(f"Update name from '{old}' to '{new}'")
    def Delete(self):
        index=self.tableView.currentIndex()
        if index is None:return
        query=QSqlQuery(self.db)
        id = int(self.model.itemData(self.model.index(index.row(),0))[0])   
        #直接执行sql     
        #query.exec(f'DELETE FROM User WHERE id={id}')
        #参数化执行
        query.prepare('DELETE FROM User WHERE Id=:id')
        query.bindValue(':id',id)
        query.exec()
        self.parent.OnShowMessage(f'Delete id:{id}')
    def Query(self):        
        self.model=QSqlQueryModel()        
        self.model.setQuery('SELECT * FROM User',self.db)
        self.model.setHeaderData(0,Qt.Orientation.Horizontal,'主键')
        self.model.setHeaderData(1,Qt.Orientation.Horizontal,'姓名')
        self.tableView.setModel(self.model)
        self.parent.OnShowMessage(f'Query count:{self.model.rowCount()}')
def runDemo(parent):
    wigdet =  Demo(parent)
    return wigdet